﻿using System;
using System.Data;
using System.Configuration;

namespace CodeArt.SharePoint.DataProvider
{
    using System.Data.Common;
    using System.Data.SqlClient;

    /// <summary>
    ///  SqlProvider
    /// </summary>
    public class SqlProvider : DbProvider, IDbProvider
    {
        private IDbConnection _connection = null;
        private IDbTransaction _transaction = null;
        private bool _isTransactionOpen = false;
        
        public SqlProvider(string dbconnectionName)
            : base(dbconnectionName)
        { }
        public SqlProvider(string dbconnectionString, int commandTimeout)
            : base(dbconnectionString, commandTimeout)
        { }


        public override IDbConnection Connection 
        {
            get { return _connection; } 
        }
        public override IDbTransaction Transaction 
        { 
            get { return _transaction; } 
        }


        public override IDbConnection OpenConnection()
        {
           if (this._connection == null)
            {
                this._connection = new SqlConnection(ConnectionString);
                try
                {
                    this._connection.Open();
                    return _connection;
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format("Unable to open connection to \"{0}\".", ConnectionString), ex);
                }
            }
            else if (this._connection.State != ConnectionState.Open)
            {
                try
                {
                    this._connection.Open();
                    return _connection;
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format("Unable to open connection to \"{0}\".", ConnectionString), ex);
                }
            }

           return _connection;
            
        }

        public override void CloseConnection()
        {
            if ((this._connection != null) && (this._connection.State == ConnectionState.Open))
            {
                this._connection.Close();
            }
            //
            this._connection = null;
        }


        public override IDbTransaction BeginTransaction()
        {
            return this.BeginTransaction(IsolationLevel.ReadCommitted);
        }

        public override IDbTransaction BeginTransaction(IsolationLevel isolationLevel)
        {
            if (this._connection == null || this._connection.State != ConnectionState.Open)
            {
                this.OpenConnection();
            }
            //
            this._transaction = this._connection.BeginTransaction(isolationLevel);
            this._isTransactionOpen = true;

            return _transaction;
        }

        public override void CommitTransaction()
        {
            this._transaction.Commit();
            this._transaction.Dispose();
            this._transaction = null;
            this._isTransactionOpen = false;
            //
            if (this._connection.State != ConnectionState.Closed)
            {
                this.CloseConnection();
            }
        }
        /// <summary>
        /// Commits the database transaction.
        /// </summary>
        /// <param name="closeConnection">Close the connection</param>
        public override void CommitTransaction(bool closeConnection)
        {
            this._transaction.Commit();
            this._transaction.Dispose();
            this._transaction = null;
            this._isTransactionOpen = false;
            //
            if (closeConnection)
            {
                if (this._connection.State != ConnectionState.Closed)
                {
                    this.CloseConnection();
                }
            }
        }

        public override void RollBackTransaction()
        {
            if (_isTransactionOpen && _transaction != null)
            {
                this._transaction.Rollback();
                this._transaction.Dispose();
                this._transaction = null;
                this._isTransactionOpen = false;
            }

            //
            if (this._connection.State != ConnectionState.Closed)
            {
                this.CloseConnection();
            }
        }

        /// <summary>
        /// Rolls back a transaction from a pending state.
        /// </summary>
        /// <param name="closeConnection">Close the connection</param>
        public override void RollBackTransaction(bool closeConnection)
        {
            if (_isTransactionOpen && _transaction != null)
            {
                this._transaction.Rollback();
                this._transaction.Dispose();
                this._transaction = null;
                this._isTransactionOpen = false;
            }
            //
            if (closeConnection)
            {
                if (this._connection.State != ConnectionState.Closed)
                {
                    this.CloseConnection();
                }
            }
        }

        public override IDbCommand CreateCommand(CommandType commandType)
        {
            IDbCommand command = new SqlCommand();
            command.CommandType = commandType;
            //this.PrepareCommand(command);
            //
            return command;
        }

        public override IDbCommand CreateCommand(string sqlText, CommandType commandType)
        {
            IDbCommand command = new SqlCommand();
            command.CommandType = commandType;
            command.CommandText = sqlText;
            return command;
        }

        public override IDbDataAdapter CreateDataAdapter()
        {
            IDbDataAdapter dataAdapter = null;
            dataAdapter = new SqlDataAdapter();
            return dataAdapter;
        }

        public override IDbDataAdapter CreateDataAdapter(IDbCommand selectCommand)
        {
            IDbDataAdapter dataAdapter = null;
            dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = selectCommand;
            //
            return dataAdapter;
        }

        public override IDataParameter CreateParameter(string name)
        {
            IDataParameter param = new SqlParameter();
            param.ParameterName = this.BuildParameterName(name);
            return param;
        }

        protected override string BuildParameterName(string name)
        {
            if (name[0] != this.ParameterToken)
            {
                return name.Insert(0, new string(this.ParameterToken, 1));
            }
            return name;
        }

        protected char ParameterToken
        {
            get { return '@'; }
        }

        #region ExecuteNonQuery
        public override int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDataParameter[] commandParams)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    SetCommandTimeout(cmd);

                    PrepareCommand(con, cmd, cmdType, cmdText, commandParams);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        public override int ExecuteNonQuery(string cmdText, params IDataParameter[] commandParams)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    SetCommandTimeout(cmd);

                    PrepareCommand(con, cmd, CommandType.Text, cmdText, commandParams);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }


        public override int ExecuteNonQuery(string cmdText)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, con))
                {
                    SetCommandTimeout(cmd);

                    con.Open();
                    
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
            }
        }
        #endregion

        #region ExecuteReader

        public override IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                SetCommandTimeout(cmd);

                PrepareCommand(con, cmd, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                con.Close();
                throw;
            }
        }

        #endregion

        #region ExecuteDataSet


        public override DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params IDataParameter[] para)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();

                using (SqlCommand cmd = new SqlCommand())
                {
                    SetCommandTimeout(cmd);

                    DataSet ds = new DataSet();
                    PrepareCommand(con, cmd, cmdType, cmdText, para);
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds);

                    return ds;
                }
            }
        }



        public override DataSet ExecuteDataSet(string cmdtext)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                using (SqlCommand cmd = new SqlCommand())
                {
                    SetCommandTimeout(cmd);

                    DataSet ds = new DataSet();
                    PrepareCommand(con, cmd, CommandType.Text, cmdtext, null);
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds);

                    return ds;
                }
            }
        }

        /// <summary>
        /// 根据指定的SQL语句,返回DATASET
        /// </summary>
        /// <param name="cmdtext">要执行带参的SQL语句</param>
        /// <param name="para">参数</param>
        /// <returns></returns>
        public override DataSet ExecuteDataSet(string cmdtext, params IDataParameter[] para)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                using (SqlCommand cmd = new SqlCommand())
                {
                    SetCommandTimeout(cmd);

                    DataSet ds = new DataSet();
                    PrepareCommand(con, cmd, CommandType.Text, cmdtext, para);
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds);

                    return ds;
                }
            }
        }


        #endregion

        #region ExecuteScalar

        public override object ExecuteScalar(CommandType cmdType, string cmdText, params IDataParameter[] commandParameters)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    SetCommandTimeout(cmd);

                    PrepareCommand(con, cmd, cmdType, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }


        public override object ExecuteScalar(string cmdText)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, con))
                {
                    SetCommandTimeout(cmd);

                    con.Open();
                   
                    return cmd.ExecuteScalar();
                }
            }
        }

        public override object ExecuteScalar(string cmdText, params IDataParameter[] commandParameters)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    SetCommandTimeout(cmd);

                    PrepareCommand(con, cmd, CommandType.Text, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }
        #endregion


        #region 建立SqlCommand


        private void SetCommandTimeout(SqlCommand cmd)
        {
            if (CommandTimeout != 30)
            {
                cmd.CommandTimeout = CommandTimeout;
            }
        }
        #endregion






    }
}
